Problem Note 33679: Excel variable names that contain multiple spaces are imported incorrectly with the XLS engine
 |  |  |  |
In SAS® 9.2 Phase I, when you use the XLS engine to import Excel variable names that contain multiple spaces, the variable names are mistranslated to the word VAR, as shown in the following output:
proc import out=test datafile="C:\files\spacevar.xls"
dbms=xls replace;
sheet=sheet1;
run;
NOTE: Variable Name Change. This is a varfield -> VAR1
NOTE: Variable Name Change. This is varfield 2 -> VAR2
SAS® 9.1.3 replaces the spaces with underscores and, thereby, imports the variable names correctly, as shown in this example:
NOTE: Variable Name Change. This is a varfield -> THIS_IS_A_VARFIELD
NOTE: Variable Name Change. This is varfield 2 -> THIS_IS_VARFIELD_2
To circumvent this problem, do one of the following:
- Remove the multiple spaces in the variable names in Excel before importing the files. Variables with a single space in the variable name import correctly.
- Use the EXCEL engine if you are on a 32-bit version of SAS 9.2, or use the EXCELCS engine on a 64-bit version of SAS 9.2.
- Contact SAS Technical Support for a program named XLSCOLUMNFIX.SAS, which renames the columns back to the underscore names.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to PC Files | Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS1M0 | 9.4 TS1M0 |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Microsoft Windows XP 64-bit Edition | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Microsoft® Windows® for x64 | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Standard Edition | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Microsoft Windows XP Professional | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Windows Vista | 9.2 TS1M0 | 9.4 TS1M0 | ||
| 64-bit Enabled AIX | 9.2 TS1M0 | 9.4 TS1M0 | ||
| 64-bit Enabled HP-UX | 9.2 TS1M0 | 9.4 TS1M0 | ||
| 64-bit Enabled Solaris | 9.2 TS1M0 | 9.4 TS1M0 | ||
| HP-UX IPF | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Linux | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Linux for x64 | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Solaris for x64 | 9.2 TS1M0 | 9.4 TS1M0 | ||
Excel variable names can contain multiple spaces, which are normally translated to underscores. However in SAS® 9.2 Phase 1, those variable names mistranslated to the name VAR.
| Type: | Problem Note |
| Priority: | medium |
| Date Modified: | 2008-10-21 14:29:12 |
| Date Created: | 2008-10-16 14:29:05 |